﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NG
{
    class SAAHandler:ISubAnalaysisArea
    {
        private string tableName = "[dbo].[SubAnalysisArea]";
        public void addSubAnalysisArea(SubAnalaysisArea sAA)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("INSERT INTO" + tbn + "(SubAreaCode,AreaCode,SubAreaName,SubAreaDiscription)VALUES("
                + "{0},{1},'{2}','{3}');",sAA.subAreaCode,sAA.areaCode,sAA.subAreaName,sAA.subAreaDesc);


            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void updateSubAnalysisArea(SubAnalaysisArea sAA)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("UPDATE" + tbn + " SET SubAreaName = '{0}, SubAreaDiscreption ={1}"
                + "WHERE SubAreaCode={2} AND AreaCode = {3}", sAA.subAreaName,sAA.subAreaDesc,sAA.subAreaName,sAA.subAreaDesc);
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void deleteSubAnalysisArea(SubAnalaysisArea sAA)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;

            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = string.Format("DELETE FROM " + tbn + " WHERE SubAreaCode = {0} AND AreaCode= {1}   ", sAA.subAreaCode,sAA.areaCode);
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public LinkedList<SubAnalaysisArea> allSubAnalysisArea(string sortedBy, bool desc)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = " {0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<SubAnalaysisArea> sAAList = new LinkedList<SubAnalaysisArea>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {

                    SubAnalaysisArea subArea = new SubAnalaysisArea(int.Parse(rdr["SubAreaCode"].ToString()),
                            int.Parse(rdr["AreaCode"].ToString()),rdr["SubAreaName"].ToString(),rdr["SubAreaDescription"].ToString());


                    sAAList.AddLast(subArea);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return sAAList;
        }

        public LinkedList<SubAnalaysisArea> allSubAnalysisArea(string sortedBy, bool desc,AnalysisArea aa)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = " {0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<SubAnalaysisArea> sAAList = new LinkedList<SubAnalaysisArea>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn+"WHERE AreaCode=" +aa.areaCode + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {

                    SubAnalaysisArea subArea = new SubAnalaysisArea(int.Parse(rdr["SubAreaCode"].ToString()),
                            int.Parse(rdr["AreaCode"].ToString()), rdr["SubAreaName"].ToString(), rdr["SubAreaDescription"].ToString());


                    sAAList.AddLast(subArea);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return sAAList;
        }
        }
    
}
